﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PBOffice.Models;

namespace PBOffice.DAL
{
    public class NewsService
    {

        public List<News> GetAllNewsByType(string type)
        {
            SqlParameter[] spa = {
                                    new SqlParameter("@Type",type)
                                };
            List<News> list = new List<News>();
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, "proc_NewsByType", spa))
            {
                while (dr.Read())
                {
                    NewsType t = new NewsType();
                    t.TypeId = (int)dr["TypeId"];
                    t.TypeName = dr["TypeName"].ToString();
                    News news = new News();
                    news.NewsId = (int)dr["NewsId"];
                    news.NewsTitle = dr["NewsTitle"].ToString();
                    news.NewsType = t;
                    news.NewsContent = dr["NewsContent"].ToString();
                    news.IsTop = (bool)dr["IsTop"];
                    news.IsCommend = (bool)dr["IsCommend"];
                    news.Hits = (int)dr["Hits"];
                    news.NewDate = Convert.ToDateTime(dr["NewDate"]);
                    news.StfCode = dr["StfCode"].ToString();
                    list.Add(news);

                }
            }
            return list;
        }
        public List<News> GetAllNewsByType(string title, string type,int pageIndex,int pageSize)
        {
            SqlParameter[] spa = {
                                     new SqlParameter("@Title",title),
                                    new SqlParameter("@Type",type),
                                    new SqlParameter("@PageIndex",pageIndex),
                                    new SqlParameter("@PageSize",pageSize)
                                };
            List<News> list = new List<News>();
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, "proc_NewsTitleType", spa))
            {
                while (dr.Read())
                {
                    NewsType t = new NewsType();
                    t.TypeId = (int)dr["TypeId"];
                    t.TypeName = dr["TypeName"].ToString();
                    News news = new News();
                    news.NewsId = (int)dr["NewsId"];
                    news.NewsTitle = dr["NewsTitle"].ToString();
                    news.NewsType = t;
                    news.NewsContent = dr["NewsContent"].ToString();
                    news.IsTop = (bool)dr["IsTop"];
                    news.IsCommend = (bool)dr["IsCommend"];
                    news.Hits = (int)dr["Hits"];
                    news.NewDate = Convert.ToDateTime(dr["NewDate"]);
                    news.StfCode = dr["StfCode"].ToString();
                    list.Add(news);

                }
            }
            return list;
        }
        public News GetNewsById(int newsId)
        {
            string sql = string.Format("select n.*,t.* from News n,NewsType t  where n.TypeId=t.TypeId and n.NewsId={0}", newsId);
            News news = new News();
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    NewsType t = new NewsType();
                    t.TypeId = (int)dr["TypeId"];
                    t.TypeName = dr["TypeName"].ToString();
                    news.NewsId = (int)dr["NewsId"];
                    news.NewsTitle = dr["NewsTitle"].ToString();
                    news.NewsType = t;
                    news.NewsContent = dr["NewsContent"].ToString();
                    news.IsTop = (bool)dr["IsTop"];
                    news.IsCommend = (bool)dr["IsCommend"];
                    news.Hits = (int)dr["Hits"];
                    news.NewDate = Convert.ToDateTime(dr["NewDate"]);
                    news.StfCode = dr["StfCode"].ToString();


                }
            }
            return news;
        }
        public void DeleteNews(int id)
        {
            string sql = string.Format("delete [News] where NewsId={0}", id);
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql);
        }
        public void UpdateNews(News news)
        {
            string sql = "update [News] set NewsTitle=@NewsTitle,NewsContent=@NewsContent,TypeId=@TypeId,IsTop=@IsTop,IsCommend=@IsCommend,Hits=@Hits,NewDate=@NewDate,StfCode=@StfCode where NewsId=@NewsId";
            SqlParameter[] spa = {
                                     new SqlParameter("@NewsId",news.NewsId),
                                new SqlParameter("@NewsTitle",news.NewsTitle),
                                new SqlParameter("@NewsContent",news.NewsContent),
                                new SqlParameter("@TypeId",news.NewsType.TypeId),
                                new SqlParameter("@IsTop",news.IsTop),
                                new SqlParameter("@IsCommend",news.IsCommend),
                                new SqlParameter("@Hits",news.Hits),
                                new SqlParameter("@NewDate",news.NewDate),
                                new SqlParameter("@StfCode",news.StfCode)
                             };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public void AddNews(News news)
        {
            string sql = "insert [News] (NewsTitle,NewsContent,TypeId,IsTop,IsCommend,Hits,NewDate,StfCode) values(@NewsTitle,@NewsContent,@TypeId,@IsTop,@IsCommend,@Hits,@NewDate,@StfCode)";
            SqlParameter[] spa = {
                                new SqlParameter("@NewsTitle",news.NewsTitle),
                                new SqlParameter("@NewsContent",news.NewsContent),
                                new SqlParameter("@TypeId",news.NewsType.TypeId),
                                new SqlParameter("@IsTop",news.IsTop),
                                new SqlParameter("@IsCommend",news.IsCommend),
                                new SqlParameter("@Hits",news.Hits),
                                new SqlParameter("@NewDate",news.NewDate),
                                new SqlParameter("@StfCode",news.StfCode)
                             };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public int GetCountByTitleAndType(string title, string type)
        {
            SqlParameter[] spa = {
                                     new SqlParameter("@Title",title),
                                     new SqlParameter("@Type",type)
                                 };
            return Convert.ToInt32(SqlHelp.ExecuteScalar(SqlHelp.connectionstring, "proc_NewsTitleTypeCount", spa));
        }

    }
}
